IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_AnnotationsSelectForEntity]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_AnnotationsSelectForEntity]
GO
create procedure [dbo].[cms_sp_AnnotationsSelectForEntity]
@EntityId int,
@EntityTypeParameter nvarchar(400)
as
declare @result table
(
	AnnotationId int,
	AuthorId int
)
insert into @result
select 
	AnnotationId,
	CreatedBy
from dbo.Annotations
where EntityId = @EntityId and 
	EntityTypeParameter = @EntityTypeParameter
select 
	Annotations.AnnotationId,
	Annotations.EntityId,
	Annotations.EntityTypeParameter,
	Annotations.AnnotationText,
	Annotations.X,
	Annotations.Y,
	Annotations.Z,
	Annotations.IsMinimized,
	Annotations.CreatedBy,
	Annotations.DateCreated,
	Annotations.LastUpdatedBy,
	Annotations.DateLastUpdated,
	Creators.UserName as UserCreatedBy
from dbo.Annotations
inner join @result as tmp
	on tmp.AnnotationId = Annotations.AnnotationId
left join dbo.Users as Creators
	on Creators.UserId = Annotations.CreatedBy
select
	AnnotationComments.AnnotationCommentId,
	AnnotationComments.AnnotationId,
	AnnotationComments.CommentText,
	AnnotationComments.OrderIndex,
	AnnotationComments.CreatedBy,
	AnnotationComments.DateCreated,
	AnnotationComments.LastUpdatedBy,
	AnnotationComments.DateLastUpdated,
	Creators.UserName as UserCreatedBy
from dbo.AnnotationComments
inner join dbo.Annotations
	on Annotations.AnnotationId = AnnotationComments.AnnotationId
inner join @result as tmp
	on tmp.AnnotationId = Annotations.AnnotationId
left join dbo.Users as Creators
	on Creators.UserId = AnnotationComments.CreatedBy
select distinct(tmp.AnnotationId),
	UserGroups.UserGroupId,
	UserGroups.SiteId,
	UserGroups.SiteGroupId,
	UserGroups.GroupName,
	UserGroups.IsEnabled,
	UserGroups.IsSystem,
	UserGroups.CreatedBy,
	UserGroups.DateCreated,
	UserGroups.LastUpdatedBy,
	UserGroups.DateLastUpdated
from dbo.UserGroups
inner join dbo.UserGroupLinks
	on UserGroupLinks.UserGroupId = UserGroups.UserGroupId
inner join dbo.Users
	on Users.UserId = UserGroupLinks.UserId
inner join @result as tmp
    on Users.UserId = tmp.AuthorId



GO

